﻿CREATE OR REPLACE FORCE VIEW "LY_HR_ZZFW"."LY_JZ_ZZFW_BMXX_PX" ("NUM", "ID", "MC", "PID", "PXH", "ZHXGSJ") AS 
SELECT "NUM","ID","MC","PID","PXH","ZHXGSJ" FROM (
SELECT ROWNUM AS NUM, T.* FROM (
SELECT DWID AS ID, DWMC AS MC, '' AS PID, 0 AS PXH, NULL AS ZHXGSJ FROM LY_JZ_ZZFW_DWXX
UNION ALL
SELECT * FROM (SELECT BMID AS ID, BMMC AS MC, SJBMID AS PID, PXH, ZHXGSJ FROM LY_JZ_ZZFW_BMXX WHERE SCBS = '0' AND BMID NOT IN (
SELECT BMID FROM LY_JZ_ZZFW_BMXX START WITH BMID IN (SELECT BMID FROM LY_JZ_ZZFW_BMXX WHERE SCBS = '1') CONNECT BY PRIOR BMID = SJBMID
) ORDER BY PXH)
) T WHERE T.ID != (SELECT DWID FROM LY_JZ_ZZFW_DWXX) START WITH T.ID = (SELECT DWID FROM LY_JZ_ZZFW_DWXX) CONNECT BY PRIOR T.ID = T.PID ORDER SIBLINGS BY PXH, CASE WHEN T.ZHXGSJ IS NULL THEN 1 ELSE 0 END, T.ZHXGSJ, T.ID);

/**
顶级单位排在第一位
未撤销的部门按照排序号和层级排序
**/